﻿//Description:装机产品类
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:Yuxj-装机产品 Zhunan-在制品
//create Date:2017-8-17
//Rewriter:
//Rewrite Date:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using External.APICalling;
using BusinessRule.DTO;
using System.Configuration;
using System.Xml;
using MES.DataGateway.DBUtility;
using System.Data;
using log4net;
using System.Data.OracleClient;
using System.Text;
using System.Collections;

namespace MESWebService
{
    public class TCGetMESActualMaterial
    {
        /// <summary>
        ///Yuxj 对于集件的产品，发动机分厂和其它分厂 装配数量的处理有逻辑差异，故需要分别处理,
        /// 获取两个DataTable后执行合并操作
        /// </summary>
        /// <param name="IssueMaterial"></param>
        /// <returns></returns>
        public DataTable GetMESIssueMaterial(string IssueMaterial)
        {
            //几个问题：1，同一子件号是否可能在总体及其它分厂同时装配 2，传入参数是否为list类型（确定为单个件号） 
            //3，未装配的子件，父件号如何显示 4，总体和其它分厂剩余数量计算逻辑不同 
            string strSql = @"
                            select nvl(cci.productname,pb.productname) parentProduct,cci.containername parentBatch, cm.productbatchno batch,nvl(im.issueqty,0) quantity,
                                   cm.qty stockQty, wf.part_revid,wf.ly2sequence 
                            from collecterpmaterial cm 
                            left join mfgorder m on m.mfgordername=cm.mfgordername
                            left join product p on p.productid=m.productid
                            left join productbase pb on pb.productbaseid=nvl(p.productbaseid,m.productbaseid) 
                            left join issueerpmaterial im on im.collecterpmaterialid=cm.collecterpmaterialid                            
                            left join containercurrentinfo cci on cci.containerid=im.containerid
                            left join workflow wf on wf.workflowid=cci.workflowid
                            left join factory f on f.factoryid=m.factoryid
                            where cm.productname='{0}' and f.factoryname<>'{1}'
                        ";
            strSql = string.Format(strSql, IssueMaterial, "装配试车厂");
            DataSet myDS = OracleHelper.Query(strSql);
            DataTable dt1 = myDS.Tables[0];//非发动机事业部数据

            strSql = @"
                            select nvl(cci.productname,pb.productname) parentProduct,u.attributevalue parentBatch, 
                                   cm.productbatchno batch,nvl(im.issueqty,0) quantity,
                                   cm.qty-nvl(imc.issueqty,0) stockQty, wf.part_revid,wf.ly2sequence 
                            from collecterpmaterial cm 
                            left join mfgorder m on m.mfgordername=cm.mfgordername
                            left join product p on p.productid=m.productid
                            left join productbase pb on pb.productbaseid=nvl(p.productbaseid,m.productbaseid) 
                            left join userattribute u on u.parentid=m.mfgorderid and u.userattributename='发动机'
                            left join issueerpmaterial im on im.collecterpmaterialid=cm.collecterpmaterialid
                            left join 
                            (select imc.collecterpmaterialid,sum(imc.issueqty) issueqty from 
                                    issueerpmaterial imc group by imc.collecterpmaterialid ) imc 
                                    on imc.collecterpmaterialid=cm.collecterpmaterialid                           
                            left join containercurrentinfo cci on cci.containerid=im.containerid
                            left join workflow wf on wf.workflowid=cci.workflowid
                            left join factory f on f.factoryid=m.factoryid
                            where cm.productname='{0}' and f.factoryname='{1}'
                        ";

            strSql = string.Format(strSql, IssueMaterial, "装配试车厂");
            myDS = OracleHelper.Query(strSql);
            DataTable dt2 = myDS.Tables[0];
            dt1.Merge(dt2);
            return dt1;
        }
        /// <summary>
        /// Zhunan根据件号获取在制批次
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetProductWipInfo(Dictionary<string, string> para)
        {
            string sql = @"
                            SELECT cci.containername batch,cci.qty quantity,wf.process_no,cci.workflowstepsequence meopNo,wf.part_revid,wf.ly2sequence
                            FROM containercurrentinfo cci
                            LEFT JOIN workflow wf ON wf.workflowid = cci.workflowid
                            WHERE cci.isinstore = 0 AND cci.containerstatus <> 2
                            ";
            if (para.ContainsKey("productname"))
            {
                sql += string.Format(" AND cci.productname = '{0}'", para["productname"]);
            }
            if (para.ContainsKey("process_no"))
            {
                sql += string.Format(" AND wf.process_no = '{0}'", para["process_no"]);
            }
            DataSet ds = OracleHelper.Query(sql);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return new DataTable();
            }
        }
    }
}